//
//  DBAccess.m
//  iPicker
//
//  Created by Admin on 19-12-10.
//  Copyright 2010 Bedrijf. All rights reserved.
//

#import "DBAccess.h"

@implementation DBAccess

- (id) init 
{
	if ((self = [super init]))
	{
		[self initializeDatabase];
	}
	return self;
}

- (void) initializeDatabase
{
	//get the database from the application bundle
	NSBundle *bundle = [NSBundle mainBundle];
	NSString *path = [bundle pathForResource:@"osiris" ofType:@"db"];
	
	//open the database
	if (sqlite3_open([path UTF8String], &database) == SQLITE_OK)
	{
		NSLog(@"Opening the database");
	}
	else 
	{
		sqlite3_close(database);
		NSAssert1(0, @"Failed to open the database: '%s'.",
				  sqlite3_errmsg(database));
	}
}

- (void) closeDatabase
{
	//close the database
	if (sqlite3_close(database) != SQLITE_OK)
	{
		NSAssert1(0, @"Error: failed to close the database: '%s'.",
				  sqlite3_errmsg(database));
	}
}
		
- (NSMutableArray *) getAllPeriods:(NSString *)year
{
	NSMutableArray *periods = [[[NSMutableArray alloc] init] autorelease];
	
	//NSString *sql = [[NSString alloc] initWithFormat: @"SELECT * FROM perioddata"];
	
	NSString *sql = [[NSString alloc] initWithFormat: @"SELECT perioddata.id,perioddata.periodname,perioddata.details,years.year,periods.period \
					 FROM perioddata,years,periods WHERE years.yearid=perioddata.yearid AND perioddata.periodid=periods.periodid AND years.year='%@'", year];
	
	
	sqlite3_stmt *statement;
	int sqlResult = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL);
	
	if(sqlResult == SQLITE_OK)
	{
		while(sqlite3_step(statement) == SQLITE_ROW)
		{
			Selection *selection = [[Selection alloc] init];
			
			char *name = (char *)sqlite3_column_text(statement, 1);
			char *details = (char *)sqlite3_column_text(statement, 2);
			char *selectedYear = (char *)sqlite3_column_text(statement, 3);
			char *selectedPeriod = (char *)sqlite3_column_text(statement, 4);
			
			selection.id = sqlite3_column_int(statement, 0);
			selection.name = (name) ? [NSString stringWithUTF8String:name] : @"";
			selection.details = (details) ? [NSString stringWithUTF8String:details] : @"";
			selection.selectedYear = (selectedYear) ? [NSString stringWithUTF8String:selectedYear] : @"";
			selection.selectedPeriod = (selectedPeriod) ? [NSString stringWithUTF8String:selectedPeriod] : @"";
			
			[periods addObject:selection];
			[selection release];
		}
		sqlite3_finalize(statement);
	}
	else 
	{
		NSLog(@"Problem with the database");
		NSLog(@"%d", sqlResult);
	}

	return periods;
}

- (Selection *) getSelectedPeriodForYear:(NSString *)year: (NSString *)period 
{
	Selection *selection = [[[Selection alloc] init] autorelease];
	
	NSString *sql = [[NSString alloc] initWithFormat: @"SELECT perioddata.id,perioddata.periodname,perioddata.details,years.year,periods.period \
					 FROM perioddata,years,periods WHERE years.yearid=perioddata.yearid AND perioddata.periodid=periods.periodid AND years.year='%@' AND periods.period='%@'" ,year ,period];
	
	sqlite3_stmt *statement;
	int sqlResult = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL);
	
	if(sqlResult == SQLITE_OK)
	{
		while(sqlite3_step(statement) == SQLITE_ROW)
		{
			//Selection *selection = [[Selection alloc] init];
			
			char *name = (char *)sqlite3_column_text(statement, 1);
			char *details = (char *)sqlite3_column_text(statement, 2);
			char *selectedYear = (char *)sqlite3_column_text(statement, 3);
			char *selectedPeriod = (char *)sqlite3_column_text(statement, 4);
			
			selection.id = sqlite3_column_int(statement, 0);
			selection.name = (name) ? [NSString stringWithUTF8String:name] : @"";
			selection.details = (details) ? [NSString stringWithUTF8String:details] : @"";
			selection.selectedYear = (selectedYear) ? [NSString stringWithUTF8String:selectedYear] : @"";
			selection.selectedPeriod = (selectedPeriod) ? [NSString stringWithUTF8String:selectedPeriod] : @"";
			
			//[selection release];
		}
		sqlite3_finalize(statement);
	}
	else 
	{
		NSLog(@"Problem with the database");
		NSLog(@"%d", sqlResult);
	}
	
	return selection;
	
	
}

@end
